﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.SqlClient;
using Follow.Utils;
using Follow.Model;
using System.Transactions;

namespace Follow.DataAccess
{
    public class MetaDAO
    {
        private SqlDataReader reader = null;
        private SqlConnection conn = null;
        private SqlCommand cmd = null;

        public MetaDAO()
        {
            conn = ConnectionUtils.getConnection();
        }


        public IList<Meta> insert(Meta meta, int idUsuario)
        {
            String sqlCommand;
            IList<Meta> Metas = new List<Meta>();

            try
            {
                conn.Open();
                sqlCommand = "insert into meta (tipo, nome) values ('" + meta.Tipo + "', '" + meta.Nome + "');";
                sqlCommand += "select * from meta where idMeta=@@IDENTITY;";

                cmd = new SqlCommand(sqlCommand, conn);
                reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    Meta met = new Meta();
                    met.IDMeta = reader.GetInt32(0);
                    met.Nome = reader.GetString(1);
                    met.Tipo = reader.GetString(2);
                    Metas.Add(met);
                }

            }
            finally
            {
                if (reader != null)
                {
                    reader.Dispose();
                    reader.Close();
                }

                if (conn != null)
                {
                    conn.Dispose();
                    conn.Close();
                }
            }

            LogDAO log = new LogDAO();
            log.insert(idUsuario, sqlCommand, "Insert", "Metas");
            return Metas;
        }


        public IList<Meta> update(Meta meta, int idUsuario)
        {
            String sqlCommand;
            IList<Meta> Metas = new List<Meta>();

            try
            {
                conn.Open();
                sqlCommand = " update meta set nome = '" + meta.Nome + "', tipo = '" + meta.Tipo + "' where idmeta = " + meta.IDMeta;
                sqlCommand += "; delete from metafuncionario where meta_idmeta =" + meta.IDMeta;
                sqlCommand += "; select * from meta where idmeta =" + meta.IDMeta;

                cmd = new SqlCommand(sqlCommand, conn);
                reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    Meta met = new Meta();
                    met.IDMeta = reader.GetInt32(0);
                    met.Tipo = reader.GetString(1);
                    met.Nome = reader.GetString(2);
                    Metas.Add(met);
                }

            }
            finally
            {
                if (reader != null)
                {
                    reader.Close();
                }
                if (conn != null)
                {
                    conn.Close();
                }
            }
            LogDAO log = new LogDAO();
            log.insert(idUsuario, sqlCommand, "Update", "Metas");
            return Metas;
        }


        public void delete(int id, int idUsuario)
        {
            String sqlCommand;
            using (var transaction = new TransactionScope())
            {
                try
                {
                    conn.Open();
                    sqlCommand = " delete from meta where idmeta = " + id;
                    cmd = new SqlCommand(sqlCommand, conn);
                    cmd.ExecuteNonQuery();
                    transaction.Complete();
                }
                catch (Exception)
                {
                    transaction.Dispose();
                    throw;
                }
                finally
                {
                    if (reader != null)
                    {
                        reader.Close();
                    }

                    if (conn != null)
                    {
                        conn.Close();
                    }
                }
            }
            LogDAO log = new LogDAO();
            log.insert(idUsuario, sqlCommand, "Delete", "Metas");
        }

        public Meta findById(int id)
        {
            Meta m = null;
            using (var transaction = new TransactionScope())
            {
                try
                {
                    conn.Open();
                    String sqlCommand = " select * from meta where idmeta = @idmeta ";
                    cmd = new SqlCommand(sqlCommand, conn);
                    cmd.Parameters.Add(new SqlParameter("idmeta", id));
                    reader = cmd.ExecuteReader();

                    if (reader.Read())
                    {
                        m = new Meta();
                        m.IDMeta = reader.GetInt32(0);
                        m.Nome = reader.GetString(1);
                        m.Tipo = reader.GetString(2);
                        
                    }
                }

                catch (Exception)
                {
                    transaction.Dispose();
                    throw;
                }

                finally
                {
                    if (reader != null)
                    {
                        reader.Close();
                    }
                    if (conn != null)
                    {
                        conn.Close();
                    }
                }
            }
            return m;
        }

        public IList<Meta> findByName(String nome)
        {
            IList<Meta> listameta = new List<Meta>();

            try
            {
                conn.Open();

                String sqlCommand = " select * from meta where nome like '%" + nome + "%' " +
                                    " order by nome ";
                cmd = new SqlCommand(sqlCommand, conn);
                reader = cmd.ExecuteReader();

                while (reader.Read())
                {
                    Meta m = new Meta();
                    m.IDMeta = reader.GetInt32(0);
                    m.Nome = reader.GetString(1);
                    m.Tipo = reader.GetString(2);
                    listameta.Add(m);
                }
            }

            finally
            {
                if (reader != null)
                {
                    reader.Close();
                }

                if (conn != null)
                {
                    conn.Close();
                }
            }
            return listameta;
        }

        public IList<Meta> findByTipoMeta (Int32 tipometa)
        {
            IList<Meta> listatipo = new List<Meta>();

            try
            {
                conn.Open();

                String sqlCommand = " select * from meta where tipo like '%" + tipometa + "%' " +
                                    " order by nome ";
                cmd = new SqlCommand(sqlCommand, conn);
                reader = cmd.ExecuteReader();

                while (reader.Read())
                {
                    Meta m = new Meta();
                    m.IDMeta = reader.GetInt32(0);
                    m.Nome = reader.GetString(1);
                    m.Tipo = reader.GetString(2);
                    listatipo.Add(m);
                }
            }

            finally
            {
                if (reader != null)
                {
                    reader.Close();
                }

                if (conn != null)
                {
                    conn.Close();
                }
            }
            return listatipo;
        }
       
        public IList<Meta> findAll()
        {
            IList<Meta> meta = new List<Meta>();

            try
            {
                conn.Open();

                String sqlCommand = " select * from meta order by nome ";
                cmd = new SqlCommand(sqlCommand, conn);
                reader = cmd.ExecuteReader();

                while (reader.Read())
                {
                    Meta met = new Meta();
                    met.IDMeta = reader.GetInt32(0);
                    met.Nome = reader.GetString(1);
                    met.Tipo = reader.GetString(2);
                    meta.Add(met);
                }
            }

            finally
            {
                if (reader != null)
                {
                    reader.Close();
                }

                if (conn != null)
                {
                    conn.Close();
                }
            }

            return meta;
        }


    }
 }
        
    
